package com.swordget.dao.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.swordget.commons.JdbcUtil;
import com.swordget.dao.PanelDao;
import com.swordget.entity.po.PanelPo;
import com.swordget.entity.vo.PanelVo;
import com.swordget.service.SwitchingDataSourcesService;
import com.swordget.service.Sys_DataSourceService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.*;

/**
 * @Author: David
 * @Date: Created in 13:38 2021/2/18
 * @Description:
 * @Modified By:
 * @Version:
 */
@Repository
public class PanelDaoImpl implements PanelDao {

    private JdbcUtil jdbcUtil;

    private DruidDataSource dataSource;

    private SwitchingDataSourcesService switchingDataSourcesService;

    private final static Logger LOG = LoggerFactory.getLogger(PanelDaoImpl.class);

    @Autowired
    public PanelDaoImpl(JdbcUtil jdbcUtil, DruidDataSource dataSource, SwitchingDataSourcesService switchingDataSourcesService) {
        this.jdbcUtil = jdbcUtil;
        this.dataSource = dataSource;
        this.switchingDataSourcesService = switchingDataSourcesService;
    }

    @Override
    public boolean savePanel(String id, String paneName, String createDate, String createTime, String loginId) {
        String command = String.format("INSERT INTO PANELMANGER(ID, PANELNAME, CREATEDATE, CREATETIME, CREATEUSER)" +
                "VALUES('%s', '%s', '%s', '%s', '%s')", id, paneName, createDate, createTime, loginId);
        return jdbcUtil.execute(command);
    }

    @Override
    public List<PanelPo> showPanelList() {
        //替换正式表名
        String command = String.format("SELECT * FROM PANELMANGER ORDER BY CREATEDATE, CREATETIME DESC");
        try (Connection conn = dataSource.getConnection();
             Statement statement = conn.createStatement();
             ResultSet resultSet = statement.executeQuery(command)) {
            LOG.info(command);
            List<PanelPo> panelPoList = new ArrayList<>();
            while (resultSet.next()) {
                panelPoList.add(PanelPo.builder()
                        .id(resultSet.getString("ID"))
                        .panelName(resultSet.getString("PANELNAME"))
                        .createDate(resultSet.getString("CREATEDATE"))
                        .createTime(resultSet.getString("CREATETIME"))
                        .createUser(resultSet.getString("CREATEUSER"))
                        .build());
            }
            return panelPoList;
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, String>> showTablePanel(String panelId) {
        String command = String.format("SELECT a.TABLENAME, a.TABLENCOM, a.SERIALNO, a.REMARKS FROM TABLEDESIGN a INNER JOIN TABLE_PANEL b on a.SERIALNO = b.TABLEID " +
                " WHERE b.PANELID = '%s'", panelId);
        try (Connection conn = dataSource.getConnection();
             Statement statement = conn.createStatement();
             ResultSet resultSet = statement.executeQuery(command)) {
            LOG.info(command);
            List<Map<String, String>> list = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, String> map = new HashMap<>(2);
                map.put("tableName", resultSet.getString("TABLENAME"));
                map.put("tableComment", resultSet.getString("TABLENCOM"));
                map.put("tableId", resultSet.getString("SERIALNO"));
                map.put("remarks", resultSet.getString("REMARKS"));
                list.add(map);
            }
            return list;
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, String>> showTablePanelByTableCn(String panelId, String tableNa, String tableCn) {

        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {

            StringBuffer sql = new StringBuffer("SELECT a.TABLENAME, a.TABLENCOM, a.SERIALNO, a.REMARKS, a.UPLOADFREQUENCY, " +
                    "a.DATASOURCEID FROM TABLEDESIGN a INNER JOIN TABLE_PANEL b on a.SERIALNO = b.TABLEID WHERE b.PANELID = ? " +
                    "AND a.REPORTSTATUS = '1' ");

            if (!StringUtils.isEmpty(tableNa)) {
                sql.append("AND a.TABLENAME like ? ");
            }

            if (!StringUtils.isEmpty(tableCn)) {
                sql.append("AND a.TABLENCOM like ? ");
            }

            LOG.info(sql.toString());

            conn = dataSource.getConnection();
            statement = conn.prepareStatement(sql.toString());


            statement.setString(1, panelId);
            int i = 1;

            if (!StringUtils.isEmpty(tableNa)) {
                i ++;
                statement.setString(i, "%" + tableNa + "%");
            }

            if (!StringUtils.isEmpty(tableCn)) {
                i ++;
                statement.setString(i, "%" + tableCn + "%");
            }


            resultSet = statement.executeQuery();

            List<Map<String, String>> list = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, String> map = new HashMap<>(2);
                map.put("tableName", resultSet.getString("TABLENAME"));
                map.put("tableComment", resultSet.getString("TABLENCOM"));
                map.put("tableId", resultSet.getString("SERIALNO"));
                map.put("remarks", resultSet.getString("REMARKS"));
                map.put("uploadFrequency", resultSet.getString("UPLOADFREQUENCY"));
                map.put("dataSourceId", resultSet.getString("DATASOURCEID"));
                list.add(map);
            }
            return list;
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
            throw new RuntimeException(e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }

                if (statement != null) {
                    statement.close();
                }

                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("程序执行过程中出现异常", e);
            }
        }
    }

    @Override
    public boolean deletePanel(String panelId) {
        String deletePanelCommand = "DELETE FROM PANELMANGER WHERE ID = '" + panelId + "'";
        String deleteTableCommand = "DELETE FROM TABLE_PANEL WHERE PANELID = '" + panelId + "'";
        return jdbcUtil.executeBatch(deletePanelCommand, deleteTableCommand);
    }

    @Override
    public boolean modifyPanelMenu(PanelVo panelVo) {
        String command = String.format("UPDATE PANELMANGER SET MENUID = '%s' WHERE ID = '%s'", panelVo.getMenuId(), panelVo.getId());
        return jdbcUtil.execute(command);
    }

    @Override
    public boolean addPanelTable(String tableId, String panelId) {
        String command = String.format("INSERT INTO TABLE_PANEL(TABLEID, PANELID) VALUES('%s', '%s')", tableId, panelId);
        return jdbcUtil.execute(command);
    }

    @Override
    public Map<String, Object> getCount(Map<String, String> map) {
        //获取表的统计数据
        String paddingSubmit = String.format("SELECT count(*) FROM %s WHERE STATUS in ('0', '-1', '-2')", map.get("tableName"));
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int submit = 0;
        try {
            conn = switchingDataSourcesService.switchingDataSources(map.get("dataSourceId"));
            ps = conn.prepareStatement(paddingSubmit);
            rs = ps.executeQuery();
            if (rs.next()) {
                submit = rs.getInt(1);
            }
        } catch (SQLException e) {
            LOG.error("程序执行过程中出现异常", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                LOG.error("程序执行过程中出现异常", e);
            }
        }

        Map<String, Object> data = new HashMap<>(3);
        data.put("name", map.get("tableComment"));
        data.put("tableId", map.get("tableId"));
        data.put("submit", submit);
        data.put("check", 0);
        data.put("pass", 0);
        data.put("code", map.get("tableName"));
        data.put("remarks", map.get("remarks"));
        data.put("uploadFrequency", map.get("uploadFrequency"));
        return data;
    }


}
